
with fine_amount as (
    select
         network_code
        ,network_name
        ,fee_type            -- 费用类型
        ,fee_type_code       -- 费用大类CODE
        ,fee_type_name       -- 费用类型名称
        ,fee_item_type       -- 费用子类
        ,fee_item_type_code  -- 费用子类CODE
        ,fee_item_type_name  -- 费用子类名称
        ,sum(amount) as amount
    from 
        spmi_dwd.dwd_ibk_fund_record_info_base_dt
    where 
        dt = '{{ execution_date | cst_ds }}'
        and fee_type_name in ('财务类', '操作类', '监察类', '客服类', '品牌类', '市场类', '网管类', '质控类', '仲裁类')
        and fee_item_type_name not regexp '使用费|奖励|加款'
    group by 
         network_code
        ,network_name
        ,fee_type          
        ,fee_type_code     
        ,fee_type_name     
        ,fee_item_type     
        ,fee_item_type_code
        ,fee_item_type_name
),
barscan_count as (
    select 
        scan_site_code
        ,scan_site
        ,count(*) as scan_cnt
    from (
        select 
            waybill_no
            ,scan_time
            ,scan_site_code
            ,scan_site
        from jms_dwd.dwd_tab_barscan_collect_base_dt
        where 
            dt = '{{ execution_date | cst_ds }}'
        
        union all 
        
        select 
            waybill_no
            ,scan_time
            ,scan_site_code
            ,scan_site
        from jms_dwd.dwd_tab_barscan_deliver_base_dt
        where 
            dt = '{{ execution_date | cst_ds }}'
    ) a 
    group by 
         scan_site_code
        ,scan_site
)
insert overwrite table  bidefault.dm_waybiil_ticket_violation_cost_dt partition(dt = '{{ execution_date | cst_ds }}')
select 
    network_code
    ,network_name
    --fee_type          
    ,fee_type_code     
    ,fee_type_name     
    -- ,fee_item_type     
    ,fee_item_type_code
    ,fee_item_type_name
    ,amount
    ,nvl(barscan_count.scan_cnt,0) as scan_cnt
from 
    fine_amount
left join 
    barscan_count on fine_amount.network_code = barscan_count.scan_site_code
distribute by 1
;
